package Dao;

import Model.Article;
import Model.Tag;
import Utils.DBUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zyp
 * date 2022-03-09
 */
public class ArticleDaoImpl implements ArticleDao {

    @Override
    public int addArticle(Article article, int userId) {
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into article values(null,?,?,?,?,0,?)";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //第二个参数指明返回递增主键的值，即文章的id值
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, article.getArticle_title());
            preparedStatement.setString(2, article.getArticle_content());
            preparedStatement.setTimestamp(3, new Timestamp(new java.util.Date().getTime()));
            preparedStatement.setString(4, article.getArticle_description());
            preparedStatement.setInt(5, userId);

            int rows = preparedStatement.executeUpdate();
            //获得文章的id
            resultSet = preparedStatement.getGeneratedKeys();

            if (rows == 1) {
                connection.commit();
            } else {
                connection.rollback();
            }

            while (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }

    @Override
    public int addTag(String tagName) {
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into tag value (null,?)";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, tagName);

            int rows = preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();

            if (rows == 1) {
                connection.commit();
            } else {
                connection.rollback();
            }

            while (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }

    @Override
    public Tag findTagByTagName(String tagName) {
        Tag tag = new Tag();

        Connection connection = DBUtils.getConnection();
        String sql = "select * from tag where tag.tag_name = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, tagName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                tag.setId(resultSet.getInt("id"));
                tag.setTagName(resultSet.getString("tag_name"));
            }

            return tag;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public int addArticleTag(int articleId, int tagId) {
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into article_tag values(null,?,?)";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, tagId);
            preparedStatement.setInt(2, articleId);

            int rows = preparedStatement.executeUpdate();

            if (rows == 1) {
                connection.commit();
                return rows;
            } else {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }

    @Override
    public List<Article> findAllArticlesByUserId(int userId) {
        List<Article> articles = new ArrayList<>();

        Connection connection = DBUtils.getConnection();
        String sql = "select * from article where article.article_user_id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Article article = new Article();
                article.setId(resultSet.getInt("id"));
                article.setArticle_content(resultSet.getString("article_content"));
                article.setArticle_description(resultSet.getString("article_description"));
                article.setArticle_title(resultSet.getString("article_title"));
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                article.setDateString(simpleDateFormat.format(new Date(resultSet.getTimestamp("article_date").getTime())));
                article.setArticle_user_id(resultSet.getInt("article_user_id"));

                articles.add(article);
            }

            return articles;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public Article findArticleById(int id) {
        Article article = new Article();

        Connection connection = DBUtils.getConnection();
        String sql = "select * from article where article.id=?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                article.setId(resultSet.getInt("id"));
                article.setArticle_content(resultSet.getString("article_content"));
                article.setArticle_description(resultSet.getString("article_description"));
                article.setArticle_title(resultSet.getString("article_title"));
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                article.setDateString(simpleDateFormat.format(new Date(resultSet.getTimestamp("article_date").getTime())));
                article.setArticle_user_id(resultSet.getInt("article_user_id"));
            }

            return article;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public boolean delArticle(Article article, int id) {
        Connection connection = DBUtils.getConnection();
        String sql = "delete from article where id=? and article_user_id=?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, article.getId());
            preparedStatement.setInt(2, article.getArticle_user_id());
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }




    @Override
    public List<Article> findAllArtic() {
        List<Article> articles = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        String sql = "select * from article";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Article article = new Article();
                article.setId(resultSet.getInt("id"));
                article.setArticle_content(resultSet.getString("article_content"));
                article.setArticle_description(resultSet.getString("article_description"));
                article.setArticle_title(resultSet.getString("article_title"));
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                article.setDateString(simpleDateFormat.format(new Date(resultSet.getTimestamp("article_date").getTime())));
                article.setArticle_user_id(resultSet.getInt("article_user_id"));
                articles.add(article);
            }
            return articles;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
